package cn.dlbj.util;

import java.io.FileInputStream;
import java.io.InputStream;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;

public class DealWithZyz {

	public static void main(String[] args) throws Exception {
		InputStream in = new FileInputStream("c:/book2.xls");
		Workbook book = Workbook.getWorkbook(in);
		Sheet sheet1 = book.getSheet(0);
		int columnCount = sheet1.getColumns();
		int rowCount = sheet1.getRows();
		String xm;
		String phones;
		String sjh;
		String email;
		String ssdz;
		String address;
		String id;
		for(int i = 2; i < rowCount; i++) {
			id = java.util.UUID.randomUUID().toString();
			xm = sheet1.getCell(0, i).getContents().trim();
			phones = dealPhone(sheet1.getCell(1, i).getContents());
			sjh = getMobile(phones);
			ssdz = sheet1.getCell(2, i).getContents().trim();
			if(ssdz.length() > 100) {
				ssdz = ssdz.substring(0, 100);
			}
			address = sheet1.getCell(3, i).getContents().trim();
			email = sheet1.getCell(4, i).getContents().trim();
			//System.out.println(xm + " " + phones + " " + sjh + " " + ssdz + " " + address + " " + email);
			System.out.println(
        			"insert into z_zyz (id,xm,sjh,email,ssdz,xkzy,password,bool,tjr) values('" + id + "','" 
        	         + xm + "','" + sjh + "','" + email + "','" 
        			 + ssdz + "','" + "" + "','666666"  + "','N','" + "刘江" + "');");
		}
	}
	
	private static String dealPhone(String oriStr) {
		String result = null;
		if(oriStr == null) {
			return result;
		}
		result = oriStr.replaceAll(" ", "");
		result = result.replaceAll(",", "/");
		result = result.replaceAll("，", "/");
		result = result.replaceAll("、", "/");
		result = result.replaceAll("-", "");
		result = result.replaceAll("-", "");
		result = result.replaceAll("‐", "");
		return result;
		//System.out.println(result);
		
	}
	
	private static String getMobile(String phones) {
		if(phones == null) {
			return null;
		}
		String[] phoneArr = phones.split("/");
		for(String phone : phoneArr) {
			//System.out.println(phone);
			phone = phone.trim();
			if(phone.length() == 11 && phone.startsWith("1")) {
				return phone;
			}
		}
		String result = phoneArr[0];
		return result;
	}

}
